import os

import pymysql
from pymysql import Connection, OperationalError
from pymysql.cursors import Cursor
from werkzeug.security import generate_password_hash

DB_HOST: str = "127.0.0.1"
DB_PORT: int = 3306
DB_USER: str = "root"
DB_PASSWD: str = "root"
DB_NAME: str = "EasyEditor_data"

conn: (Connection | None) = None
cursor: (Cursor | None) = None


def connect():
    """
    Connect to the MySQL database for future usage.
    """
    global conn, cursor
    if conn is not None:
        disconnect()

    try:
        conn = pymysql.connect(host=DB_HOST, port=DB_PORT, user=DB_USER, passwd=DB_PASSWD,
                               charset='utf8', db=DB_NAME)
        cursor = conn.cursor()
    except OperationalError as e:
        print("[!] Failed to connect to MySQL database.")
        print(f"[!] Details: {e}")


def disconnect():
    """
    Disconnect from the backend database.
    """
    global conn, cursor
    if conn is not None:
        conn.commit()
        cursor.close()
        conn.close()

        cursor = None
        conn = None


def add_user(username: str, password: str):
    """
    Add a new user to the database.

    :param username: the name of the user (currently also the display name)
    :param password: the password of the user
    """
    global conn, cursor
    if conn is None:
        connect()

    sql: str = "insert into users (username, passwd) values (%s, %s)"
    cursor.execute(sql, (username, generate_password_hash(password)))
    disconnect()


def get_token(usage: str) -> str:
    """
    Fetch a token from the database.

    :param usage: the usage of the token
    :return: the specified token
    :except OperationalError: When a token of specified usage is not found.
    """
    global conn, cursor
    if conn is None:
        connect()

    sql: str = "select * from tokens where token_usage = %s"
    count: int = cursor.execute(sql, usage)

    if count == 0:
        raise RuntimeError(f"Cannot find a valid token for usage \"{usage}\".")

    # (id, usage, token)
    result: str = cursor.fetchone()[2]
    disconnect()
    return result


def get_user(username: str) -> (int, str, str):
    """
    Fetch a user from the database.

    :param username: the specified name of the user
    :return:
    :except OperationalError: When a user with specified username is not found.
    """
    global conn, cursor
    if conn is None:
        connect()

    sql: str = "select * from users where username = %s"
    count: int = cursor.execute(sql, username)

    if count == 0:
        raise RuntimeError(f"Cannot find a user with the name of \"{username}\".")

    user = cursor.fetchone()
    disconnect()
    return user


def get_documents(username: str) -> (int, list[str]):
    """
    Fetch a list of documents from the database.
    :param username: the specific username
    :return: A tuple which contains the count of documents and a list of their titles
    """
    global conn, cursor
    if conn is None:
        connect()

    sql: str = "select `title` from doc_path where username = %s"
    count: int = cursor.execute(sql, username)
    entries: list[str] = []
    results = cursor.fetchall()

    for i in results:
        # Title
        entries.append(i[0])

    disconnect()
    return count, entries


def get_content(username: str, title: str) -> str:
    global conn, cursor
    if conn is None:
        connect()

    sql: str = "select `file_name` from doc_path where username = %s and title = %s"
    count: int = cursor.execute(sql, (username, title))

    if count == 0:
        raise RuntimeError(f"Cannot find a document with the name {title}.")

    result = cursor.fetchone()
    output: str = ""

    with open(os.path.join("./uploads/docs", username, result[0])) as f:
        output = f.read()

    disconnect()
    return output
